package com.supermap.dataservice.file;

import com.supermap.dataservice.util.Point;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2019年12月31 10:29
 * @email 1301584033@qq.com
 */
@Mapper
public interface StationMapper {

    @Select("select * from t_life_index where cityCode =#{stationId}   order by forecastTime desc limit 1")
    List<Map<String,Object>> getLifeInfo(@Param("stationId") String stationId);
    // 疑似火点
    @Select("select * from cagmpisards.t_firemonitor t where t.areaCode like concat(#{areaCode},'%') and t.cdate=#{date}")
    List<Map<String,Object>> getFiremonitor(@Param("areaCode") String areaCode,@Param("date")  String date);

    // 风险灾害区划类型
    @Select("select m.code,m.name from cagmpisards.master_codeinfo m where m.cid = 20")
    List<Map<String,Object>> getDisasteRiskType();

    //好产品
    @Select("select * from cagmpisards.t_goodproduct t where t.areacode like concat(#{areaCode},'%')")
    List<Map<String,Object>> getGoodProduct(@Param("areaCode") String areaCode);

    @Select("select t2.showName,t2.latitude as lat,t2.longitude as lon from t_vendor_user t1\n" +
            "inner join t_user t2\n" +
            "on t1.userid=t2.id\n" +
            "inner join t_area t3\n" +
            "on t2.areaCode=t3.areacode\n" +
            "where if(#{areaCode} is null,true,t3.areacode like concat(#{areaCode},'%') ) group by t1.w_loginName")
    List<Map<String,Object>> getVendorList(@Param("areaCode") String areaCode);
    @Select("select t1.element_code,t1.product_date,t1.json_url from cagmpisards.t_grid_product t1 \n" +
            "where t1.area_code = if(left(#{areaCode},2)= '50',#{areaCode},left(#{areaCode},2)) and\n" +
            "t1.element_code=#{gridCode}  and t1.product_date=#{data}")
    List<Map<String,Object>> getGridProduct(@Param("areaCode") String areaCode,@Param("data")String data,@Param("gridCode")String gridCode);
    @Select("")
    List<Map<String,Object>> getPubertyDate(String crop);
    //当年
    @Select("")
    List<Map<String,Object>> getYearPubertyDate(@Param("") String crop);

    @Select("select Station_Id_C stationNo,lat y ,lon x from guzy.sta_info_surf_chn_n")
    List<Point> getAll();
    @Select("select V01000 stationNo,V05001 y,V05003 x from cagmpisards.t_soil_station")
    List<Point> getSoilAll();
    @Select("select group_concat(longitude,',',latitude) from cagmpisards.t_area  where areacode=#{areaCode}")
    String  getAreaCode(@Param("areaCode")String areaCode);

    @Select("select  \n" +
            "case \n" +
            "when Validtime = 24 then '今天'\n" +
            "when Validtime = 48 then '明天'\n" +
            "when Validtime = 72 then '后天'\n" +
            "when weeks = 1 then '周一'\n" +
            "when weeks = 2 then '周二'\n" +
            "when weeks = 3 then '周三'\n" +
            "when weeks = 4 then '周四'\n" +
            "when weeks = 5 then '周五'\n" +
            "when weeks = 6 then '周六'\n" +
            "when weeks = 0 then '周日'\n" +
            "end weeks,TEM_Max,TEM_Min,forecastDate,weather,WIN_D,WIN_S,WEA_CODE\n" +
            "from\n" +
            "(select\n" +
            "t1.TEM_Max,t1.TEM_Min,Validtime,\n" +
            "case \n" +
            "when t1.Validtime = 24 then date_format((date_add(sysdate(), interval 0 day)),'%w')\n" +
            "when t1.Validtime = 48  then date_format((date_add(sysdate(), interval 1 day)),'%w')\n" +
            "when t1.Validtime = 72 then date_format((date_add(sysdate(), interval 2 day)),'%w')\n" +
            "when t1.Validtime = 96 then date_format((date_add(sysdate(), interval 3 day)),'%w')\n" +
            "when t1.Validtime = 120 then date_format((date_add(sysdate(), interval 4 day)),'%w')\n" +
            "when t1.Validtime = 144 then date_format((date_add(sysdate(), interval 5 day)),'%w')\n" +
            "when t1.Validtime = 168 then date_format((date_add(sysdate(), interval 6 day)),'%w')\n" +
            "end weeks,\n" +
            "case\n" +
            "when t1.Validtime = 24 then date_format(sysdate(),'%m/%d')\n" +
            "when t1.Validtime = 48 then date_format((date_add(sysdate(), interval 1 day)),'%m/%d')\n" +
            "when t1.Validtime = 72 then date_format((date_add(sysdate(), interval 2 day)),'%m/%d')\n" +
            "when t1.Validtime = 96 then date_format((date_add(sysdate(), interval 3 day)),'%m/%d')\n" +
            "when t1.Validtime = 120 then date_format((date_add(sysdate(), interval 4 day)),'%m/%d')\n" +
            "when t1.Validtime = 144 then date_format((date_add(sysdate(), interval 5 day)),'%m/%d')\n" +
            "when t1.Validtime = 168 then date_format((date_add(sysdate(), interval 6 day)),'%m/%d')\n" +
            "end forecastDate,\n" +
            "if(t2.codeName is null,'阴',t2.codeName) weather,\n" +
            "if(t3.codeName is null,'缺测',t3.codeName) WIN_D,\n" +
            "if(t4.codeName is null,'缺测',t4.codeName) WIN_S,\n" +
            "if(t2.code is null,'2',t2.code) WEA_CODE \n" +
            "from t_station_forcast t1 \n" +
            "left join t_phenomena t2 on left(t1.WEP,LOCATE('.', t1.WEP)-1)  = t2.code and t2.`type` = 0\n" +
            "left join t_phenomena t3 on left(t1.WIN_D,LOCATE('.', t1.WIN_D)-1)  = t3.code and t3.type = 1\n" +
            "left join t_phenomena t4 on left(t1.WIN_S,LOCATE('.', t1.WIN_S)-1)  = t4.code and t4.type = 2\n" +
            "where t1.Datetime = (select max(t.Datetime) from t_station_forcast t) and  t1.Station_Id_C = #{stationId}) tt order by forecastDate")
    List<Map<String,Object>> getFrecastDataList(@Param("stationId") String stationId);
    @InsertProvider(type = StationMapperSQL.class, method = "insertListSQL")
    Integer insertList(@Param("forcastList") List<StationForcast> forcastList);

    @InsertProvider(type = StationMapperSQL.class, method = "insertListFiremonitorsSQL")
    Integer insertListFiremonitors(@Param("firemonitors") List<Firemonitor> firemonitors);

    @Select("select t.areacode from t_area t where t.areaname like concat(#{townName},'%') ")
    String getAreaCodebYtownName(@Param("townName") String townName);

    @Select("select * from surf_chn_day_mmut_19812010 s where s.Station_Id_C = #{stationId} and s.mon = month(now()) and s.day = day(now());")
    List<Map<String,Object>> getSurfchndayList(@Param("stationId") String stationId);

    //获取5天预报(专家经验产品)
    @Select("   select Station_Id_C,group_concat(forecastDate order by forecastDate) forecastDate,\n" +
            "\t\t      group_concat(TEM order by forecastDate) TEM,group_concat(TEM_Max order by forecastDate) TEM_Max ,\n" +
            "\t\t      group_concat(TEM_Min order by forecastDate) TEM_Min,group_concat(PRE order by forecastDate) PRE,areaN,dem \n" +
            "            from\n" +
            "            (select\n" +
            "            t1.Station_Id_C, t1.TEM_Max,t1.TEM_Min,t1.TEM,           \n" +
            "            case\n" +
            "            when t1.Validtime = 24 then date_format(sysdate(),'%m/%d')\n" +
            "            when t1.Validtime = 48 then date_format((date_add(sysdate(), interval 1 day)),'%m/%d')\n" +
            "            when t1.Validtime = 72 then date_format((date_add(sysdate(), interval 2 day)),'%m/%d')\n" +
            "            when t1.Validtime = 96 then date_format((date_add(sysdate(), interval 3 day)),'%m/%d')\n" +
            "            when t1.Validtime = 120 then date_format((date_add(sysdate(), interval 4 day)),'%m/%d')\n" +
            "            end forecastDate,\n" +
            "            if(t1.pre = '999.9','缺测',t1.pre) pre,t2.dem,\n" +
            "\t\t\t\tcase t2.localtype\n" +
            "\t\t\t\twhen 1 then '渝东北'\n" +
            "\t\t\t\twhen 2 then '渝东南'\n" +
            "\t\t\t\twhen 3 then '中西部'\n" +
            "\t\t\t\telse '主城'\n" +
            "\t\t\t\tend  areaN    \n" +
            "            from t_station_forcast t1      \n" +
            "            inner join t_expert_station t2 on t1.Station_Id_C =t2.stationnum\n" +
            "            where t1.Datetime = (select max(t.Datetime) from t_station_forcast t)\t\t\t\t\n" +
            "\t\t\t\t ) tt group by Station_Id_C order by areaN,dem;")
    List<Map<String,Object>> get5DayForcast();

    // 气候资源类型
    @Select("select m.code,m.name from cagmpisards.master_codeinfo m where m.cid = 21")
    List<Map<String,Object>> getClimaticResourcesType();

    // 获取一张图二级数据菜单
    @Select("select m.code,m.name,imgs,imgc from cagmpisards.sys_onemap_second_menu m where m.menucode = #{tcode}")
    List<Map<String,Object>> getOneMapSecondMenu(@Param("tcode") String tcode);

    class StationMapperSQL {
        /**
         * 多条消息入库
         */
        public String insertListFiremonitorsSQL(Map<String, Object> map) {
            List<Firemonitor> firemonitors = (List<Firemonitor>) map.get("firemonitors");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append(" replace  into t_firemonitor(cdate,cvalue,areaCode,town,city,lon,lat) ");
            stringBuilder.append(" values ");
            if (firemonitors != null && firemonitors.size() > 0) {
                for (Firemonitor firemonitor : firemonitors) {
                    stringBuilder.append(" ('"+firemonitor.getCdate()+"','" + firemonitor.getCvalue() + "'," +
                            "'" + firemonitor.getAreaCode() + "','" + firemonitor.getTown() + "','" + firemonitor.getCity() + "'," + firemonitor.getLon() + "," + firemonitor.getLat()+"),");
                }
            }
            return stringBuilder.substring(0, stringBuilder.lastIndexOf(",")).toString();
        }
        /**
         * 多条消息入库
         */
        public String insertListSQL(Map<String, Object> map) {
            List<StationForcast> startionList = (List<StationForcast>) map.get("forcastList");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append(" insert ignore into t_station_forcast(Station_Id_C,Datetime,Validtime,TEM_Max,TEM_Min,PRE,WIN_D,WIN_S,WEP) ");
            stringBuilder.append(" values ");
            if (startionList != null && startionList.size() > 0) {
                for (StationForcast startion : startionList) {
                    stringBuilder.append(" ( '" + startion.getStation_Id_C() + "'," + startion.getDatetime() + "," +
                            "'" + startion.getValidtime() + "','" + startion.getTEM_Max() + "','" + startion.getTEM_Min()+ "','" + startion.getPRE() + "','" + startion.getWIN_D() + "','" + startion.getWIN_S() + "','" + startion.getWEP() + "'),");
                }
            }
            return stringBuilder.substring(0, stringBuilder.lastIndexOf(",")).toString();
        }
    }
}
